CREATE TABLE [dbo].[Attachments](
	[MailQueueId] [uniqueidentifier] NOT NULL,
	[AttachmentId] [uniqueidentifier] NOT NULL,
	[AttachmentName] [varchar](300) NOT NULL,
	[Attachment] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[MailQueue](
	[MailQueueID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[MailProcessId] [uniqueidentifier] NULL,
	[MailCreateTime] [datetime] NULL,
	[MailProcessTime] [datetime] NULL,
	[MailStatus] [int] NULL,
	[Priority] [int] NULL,
	[MailFormat] [int] NULL,
	[Sender] [varchar](100) NULL,
	[FromName] [varchar](50) NOT NULL,
	[FromMailAddress] [varchar](100) NOT NULL,
	[ToMailAddress] [varchar](512) NOT NULL,
	[CCList] [varchar](512) NULL,
	[BccList] [varchar](512) NULL,
	[Subject] [varchar](256) NULL,
	[Body] [text] NULL,
	[ForeignKey1] [int] NULL,
	[ForeignKey2] [int] NULL,
	[ForeignKey3] [varchar](50) NULL,
	[Label] [varchar](50) NULL,
	[RetryCount] [int] NULL,
 CONSTRAINT [PK_MailQueueNew] PRIMARY KEY CLUSTERED 
(
	[MailQueueID] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MailQueue] ADD  CONSTRAINT [DF_MailQueueNew_MailQueueID]  DEFAULT (newid()) FOR [MailQueueID]
GO

ALTER TABLE [dbo].[MailQueue] ADD  CONSTRAINT [DF_MailQueueNew_MailCreateTime]  DEFAULT (getdate()) FOR [MailCreateTime]
GO

ALTER TABLE [dbo].[MailQueue] ADD  CONSTRAINT [DF_MailQueueNew_RetryCount]  DEFAULT (0) FOR [RetryCount]
GO

CREATE TRIGGER [dbo].[ProcessTimeStamp] ON [dbo].[MailQueue]
FOR UPDATE
AS
BEGIN
IF UPDATE (MailStatus)
BEGIN
	UPDATE MailQueue SET MailProcessTime = getDate() WHERE
	MailQueueId IN (SELECT i.MailQueueId FROM inserted i)	
END
END

GO

CREATE TABLE [dbo].[Settings](
	[smtpserver] [varchar](300) NULL,
	[smtpuser] [varchar](300) NULL,
	[smtppass] [varchar](50) NULL,
	[smtpport] [int] NULL,
	[smtpssl] [bit] NULL,
	[smtponbehalf] [varchar](300) NULL,
	[smtpdummy] [varchar](300) NULL,
	[pause] [bit] NULL,
	[maxbactchsize] [int] NULL,
	[sendinterval] [int] NULL,
	[settingsname] [varchar](50) NULL,
	[iscurrentsetting] [bit] NULL,
	[retries] [int] NULL,
	[retryinterval] [int] NULL,
	[purgeafterdays] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Settings] VALUES ('127.0.0.1', null, null, 25, 0, null, null, 0, 0, 3000, 'Current setting', 1, 3, 0, 180)

GO

INSERT INTO [dbo].[Settings] VALUES ('smtp.gmail.com', 'mymailqueueuser@gmail.com', 'mymailqueueuserpassword', 587, 1, 'mymailqueueuser@gmail.com', null, 1, 0, 3000, 'Google gmail smtp', 0, 3, 0, 180)